﻿## Get-InactiveActiveSyncUsers report
## Scheduler Usage: powershell -command "& {C:\WINDOWS\Scripts\Get-InactiveActiveSyncUsers.ps1 -SendEmail}"
## Command Line Usage (no email): .\Get-InactiveActiveSyncUsers.ps1
## Command Line Usage (default email): .\Get-InactiveActiveSyncUsers.ps1 -SendEmail
## Command Line Usage (one-off email): .\Get-InactiveActiveSyncUsers.ps1 -SendEmail name@domain.com
##

param ([switch]$SendEmail , [string]$OneOffEmailAddr = "default")
# By default do not send email.
cls
$DebugPreference = "SilentlyContinue"  #This is default
#$DebugPreference = "Continue"  #This causes more output
#$DebugPreference = "Inquire"  #This causes output and queries to continue or stop
#$DebugPreference = "Stop"  #This causes output and stops


## This script produces a list of all enable ActiveSync users and shows whether there is recent activity.  
## David Ray - mm/dd/yyyy
Write-Debug "Starting Report Script"
Write-Debug "Setting configuration variables"

## Setup Variables ######
#Enter addresses in array form. i.e. @("name1@dom1.com","name2@dom2.com") if none use @("")
#$defaultTo = ("name1@dom.tld","name2@dom.tld")
#$defaultCc = ("copy1@dom.tld","copy2@dom.tld")
#$defaultBcc = ("bcc1@dom.tld","bcc2@dom.tld")
$defaultTo = @("CHWIT-EnterpriseSecurityAdministration@chw.edu")
$defaultCc = @("DL-GIS-MessagingHealthcare1@ps.net","DL-GIS-MessagingWireless@ps.net","Bill_Branson@dell.com")
$defaultBcc = @("ExchangeOp@ps.net")
## from should be "Exchange@ps.net" (not in Array form)
$defaultFrom = "ExchangeOp@ps.net"
#the mail subject 
$subject = "CHW Monthly ActiveSync Usage Report"
#The body of the message
$emailMessage = "The attached file reports all enabled ActiveSync devices and indicates whether there has been recent usage along with other information about the account."
#relay server
$SMTPserver = "smtp-relay.chw.edu"
$reportName = "CHWActiveSyncUsageReport" + $(Get-Date -Format "yyMMdd")
$reportPath = "E:\Reports\ActiveSyncReport"
## End of Setup Variables ######

#Load up the Report Library Functions.  ReportLibrary.ps1 must be in the same dir as the report script
Write-Debug "Load Report Libary Functions"
#. ((Split-Path -Parent (Resolve-Path -Path $MyInvocation.InvocationName)) + "\ReportLibrary.ps1")
$ScriptName = $MyInvocation.MyCommand.Name
$ScriptDir = Split-Path $MyInvocation.MyCommand.Path
. "$ScriptDir\ReportLibrary.ps1"


######
# Here is the main data gathering portion of the script
Write-Debug "Running command to get data"
$GetVariable = Get-User  -ResultSize unlimited david.ray.002 ## Get the basic information

$ReportVariable = @() ## Create empty array for result

## Loop through basic info populating ReportObject
Write-Debug "Entering loop to build data list"
Write-Host "Retrieving ActiveSync Usage Data from recent report"
$AsyncActive = Import-Csv \\chw-cas-001\admin$\scripts\CHWActiveSyncReport.csv `
	| Select-Object @{Name="SamAccountName";Expression={$_.UserID}},DisplayName,Alias,Phone,@{Name="PrimarySMTPAddress";Expression={$_.Email}}  `
	| Sort-Object SamAccountName	

## Get list of all users enabled for ActiveSync selecting a subset of fields
Write-Host "Getting all users with ActiveSync enabled"
$AsyncEnabled = Get-CASMailbox -ResultSize Unlimited -Filter {ActiveSyncEnabled -eq $true} `
	| Get-Mailbox | Select-Object SamAccountName,DisplayName,Alias,@{Name="PrimarySMTPAddress";Expression={$_.PrimarySMTPAddress.ToString()}},Servername,ExchangeUserAccountControl,RecipientTypeDetails `
	| Sort-Object SamAccountName

Write-Host "Correlate lists and build report data array"
## inactiveUsers are the result of the Compare-Object command that have "<=" in the SideIndicator field
$InactiveUsers = Compare-Object $AsyncEnabled $AsyncActive -Property Alias | Where-Object {$_.SideIndicator -eq "<="} 

## build an array of the alias's from inactiveUsers
$InactAlias = @()
$InactiveUsers | ForEach-Object {$InactAlias += $_.alias}
$ActAlias = @()
$AsyncActive | ForEach-Object {$ActAlias += $_.alias}

##empty array for the final result
$AllAsync = @()
#$AsyncUser = New-Object PSObject  ##the objects that will go into the final result array
Foreach ($AsyncUser1 in $AsyncEnabled) {   ## loop through everyone in the AsyncEnabled collection
	if (("david.ray.002","async2003","async2007") -contains $AsyncUser1.alias -or $AsyncUser1.alias -like "CAS_*") {continue} #skip test users
	elseif ($ActAlias -contains $AsyncUser1.alias) {
		## for each active enabled user loop through the activity collection for the user (gets users with multiple devices)
		Foreach ($AsyncUser in $AsyncActive | Where-Object {$_.alias -eq $AsyncUser1.alias}) {
			$AUser = New-Object PSObject
			$AUser | Add-Member -MemberType NoteProperty -Name Displayname -Value $AsyncUser.Displayname
			$AUser | Add-Member -MemberType NoteProperty -Name UserID -Value $AsyncUser.SamAccountName
			$AUser | Add-Member -MemberType NoteProperty -Name Alias -Value $AsyncUser.Alias
			$AUActive = if ($inactAlias -contains $AsyncUser.Alias) {"No"} else {"Yes"}
			$AUser | Add-Member -MemberType NoteProperty -Name "On Recent 30-Day Activity Report" -Value $AUActive
			#$AUser | Add-Member -MemberType NoteProperty -Name Phone -Value $AsyncActiveHash.($AsyncUser.Alias)
			$AUser | Add-Member -MemberType NoteProperty -Name Phone -Value $AsyncUser.Phone
			$AUser | Add-Member -MemberType NoteProperty -Name "Exchange Server" -Value $AsyncUser1.Servername
			$AUserMailboxType = if ($AsyncUser1.RecipientTypeDetails -eq "LegacyMailbox") {"2003"} else {"2007"}
			$AUser | Add-Member -MemberType NoteProperty -Name "Exchange Version" -Value $AUserMailboxType
			$AUser | Add-Member -MemberType NoteProperty -Name Email -Value $AsyncUser.PrimarySMTPAddress
			$AUStatus = if ($AsyncUser.exchangeuseraccountcontrol -eq "AccountDisabled") {"Disabled"} else {"Enabled"}
			$AUser | Add-Member -MemberType NoteProperty -Name "Account Status" -Value $AUStatus
			if ($AUserMailboxType -eq "2007") {$ASyncPolicy = (Get-CASMailbox $AsyncUser1.Alias).ActiveSyncMailboxPolicy} else {$aSyncPolicy = "<on 2003>"}
			$AUser | Add-Member -MemberType NoteProperty -Name "ActiveSync Mailbox Policy" -Value $ASyncPolicy
			$AUser | Add-Member -MemberType NoteProperty -Name City	-Value (Get-User $AsyncUser1.Alias).City
			$AUser | Add-Member -MemberType NoteProperty -Name Office -Value (Get-User $AsyncUser1.Alias).Office
			if ( (Get-ActiveSyncDeviceStatistics -Mailbox $AsyncUser1.alias) -eq $null) {$ASyncServer = "OWA 2003"} else {$ASyncServer = "CAS 2007"}
			$AUser | Add-Member -MemberType NoteProperty -Name "Async Server" -Value $ASyncServer
			$AllAsync += $AUser
		}
	}
	else {
	## for each inactive enabled user
		$AUser = New-Object PSObject
		$AUser | Add-Member -MemberType NoteProperty -Name Displayname -Value $AsyncUser1.Displayname
		$AUser | Add-Member -MemberType NoteProperty -Name UserID -Value $AsyncUser1.SamAccountName
		$AUser | Add-Member -MemberType NoteProperty -Name Alias -Value $AsyncUser1.Alias
		$AUActive = if ($inactAlias -contains $AsyncUser1.Alias) {"No"} else {"Yes"}
		$AUser | Add-Member -MemberType NoteProperty -Name "On Recent 30-Day Activity Report" -Value $AUActive
		$AUser | Add-Member -MemberType NoteProperty -Name Phone -Value $AsyncUser1.Phone
		$AUser | Add-Member -MemberType NoteProperty -Name "Exchange Server" -Value $AsyncUser1.Servername
		$AUserMailboxType = if ($AsyncUser1.RecipientTypeDetails -eq "LegacyMailbox") {"2003"} else {"2007"}
		$AUser | Add-Member -MemberType NoteProperty -Name "Exchange Version" -Value $AUserMailboxType
		$AUser | Add-Member -MemberType NoteProperty -Name Email -Value $AsyncUser1.PrimarySMTPAddress
		$AUStatus = if ($AsyncUser1.exchangeuseraccountcontrol -eq "AccountDisabled") {"Disabled"} else {"Enabled"}
		$AUser | Add-Member -MemberType NoteProperty -Name "Account Status" -Value $AUStatus
		
		
		if ($AUserMailboxType -eq "2007") {$ASyncPolicy = (Get-CASMailbox $AsyncUser1.Alias).ActiveSyncMailboxPolicy} else {$ASyncPolicy = "<on 2003>"}
		$AUser | Add-Member -MemberType NoteProperty -Name "ActiveSync Mailbox Policy" -Value $ASyncPolicy
		$AUser | Add-Member -MemberType NoteProperty -Name City	-Value (Get-User $AsyncUser1.Alias).City
		$AUser | Add-Member -MemberType NoteProperty -Name Office -Value (Get-User $AsyncUser1.Alias).Office
		$AUser | Add-Member -MemberType NoteProperty -Name "Async Server" -Value ""
		$AllAsync += $AUser
	}
}
$AllAsync | Export-Csv -NoTypeInformation "$($reportPath)\$($reportName).csv"

## Save all this out to Excel and prettify it some.

## Excel constants that will help us
Write-Debug "Call Excel Var Function"
Set-ExcelVars

# Create an instance of Excel
Write-Debug "Call function to open Excel with CSV"
Open-ExcelCSV $reportPath $reportName

# Add a little formatting to the column header
Write-Debug "Call function to format header"
Set-ExcelHeaderFormat

## Examples of conditional formatting

##$bucket= $Worksheet.Range("H:H").FormatConditions.Add($xlCellValue, $xlGreaterEqual, 60)
##$Worksheet.Range("H:H").FormatConditions.Item(1).Interior.Color = 13551615
##$Worksheet.Range("H:H").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
##$Worksheet.Range("H:H").FormatConditions.Item(1).Interior.TintAndShade = 0
##$Worksheet.Range("H:H").FormatConditions.Item(1).Font.Color = -16383844
##$Worksheet.Range("H:H").FormatConditions.Item(1).Font.TintAndShade = 0

##$bucket= $Worksheet.Range("L:L").FormatConditions.Add($xlCellValue, $xlEqual, '="No"')
##$Worksheet.Range("L:L").FormatConditions.Item(1).Interior.Color = 13551615
##$Worksheet.Range("L:L").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
##$Worksheet.Range("L:L").FormatConditions.Item(1).Interior.TintAndShade = 0
##$Worksheet.Range("L:L").FormatConditions.Item(1).Font.Color = -16383844
##$Worksheet.Range("L:L").FormatConditions.Item(1).Font.TintAndShade = 0

##$bucket = $Worksheet.Range("L:L").FormatConditions.Add($xlCellValue, $xlEqual, '="Yes"')
##$Worksheet.Range("L:L").FormatConditions.Item(2).Interior.Color = 13561798
##$Worksheet.Range("L:L").FormatConditions.Item(2).Interior.PatternColorIndex = $xlAutomatic
##$Worksheet.Range("L:L").FormatConditions.Item(2).Interior.TintAndShade = 0
##$Worksheet.Range("L:L").FormatConditions.Item(2).Font.Color = -16752384
##$Worksheet.Range("L:L").FormatConditions.Item(2).Font.TintAndShade = 0

$bucket= $Worksheet.Range("D:D").FormatConditions.Add($xlCellValue, $xlEqual, '="No"')
$Worksheet.Range("D:D").FormatConditions.Item(1).Interior.Color = 13551615
$Worksheet.Range("D:D").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
$Worksheet.Range("D:D").FormatConditions.Item(1).Interior.TintAndShade = 0
$Worksheet.Range("D:D").FormatConditions.Item(1).Font.Color = -16383844
$Worksheet.Range("D:D").FormatConditions.Item(1).Font.TintAndShade = 0


$bucket = $Worksheet.Range("D:D").FormatConditions.Add($xlCellValue, $xlEqual, '="Yes"')
$Worksheet.Range("D:D").FormatConditions.Item(2).Interior.Color = 13561798
$Worksheet.Range("D:D").FormatConditions.Item(2).Interior.PatternColorIndex = $xlAutomatic
$Worksheet.Range("D:D").FormatConditions.Item(2).Interior.TintAndShade = 0
$Worksheet.Range("D:D").FormatConditions.Item(2).Font.Color = -16752384
$Worksheet.Range("D:D").FormatConditions.Item(2).Font.TintAndShade = 0

# Make Columns wide enough for content
Write-Debug "Calling function to autofit columns"
Set-ExcelAutofitColumns

#Save the Excel File. Use format $xlOpenXMLWorkbook for normal xlsx and $xlExcel12 for xlsb
Write-Debug "Calling function to save file"
Save-Excel $reportPath $reportName $xlOpenXMLWorkbook
#Save-Excel makes available $reportFullName for sending

## Send Mail
if ($sendEmail.IsPresent) {
	Write-Debug "Calling send email function"
	Send-Email $OneOffEmailAddr $defaultTo $defaultCc $defaultBcc $defaultFrom `
					$subject $emailmessage $reportFullName $SMTPserver
}
Write-Debug "End of Report script"